package com.etgraph.msof;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.etgraph.recap.msof.strategy.ExcelTemplateCreatorContext;
import com.etgraph.recap.msof.template.creator.ExcelTemplateCreator;
import com.etgraph.recap.msof.template.creator.SXSSFExcelTemplateCreator;
import com.etgraph.recap.msof.template.griditem.RecapItem;

/**
 * Hello world!
 * 
 */
public class App {
	public static void main(String[] args) {
		// createStyleExcel();
		// createNormalExcel();
		templateCreatorTest();
		// testAuto();
	}

	public static void createStyleExcel() {
		try {
			// create a new file
			FileOutputStream out = new FileOutputStream("workbook.xls");
			// create a new workbook
			Workbook wb = new HSSFWorkbook();
			// create a new sheet
			Sheet s = wb.createSheet();
			// declare a row object reference
			Row r = null;
			// declare a cell object reference
			Cell c = null;
			// create 3 cell styles
			CellStyle cs = wb.createCellStyle();
			CellStyle cs2 = wb.createCellStyle();
			CellStyle cs3 = wb.createCellStyle();
			DataFormat df = wb.createDataFormat();
			// create 2 fonts objects
			Font f = wb.createFont();
			Font f2 = wb.createFont();

			// set font 1 to 12 point type
			f.setFontHeightInPoints((short) 12);
			// make it blue
			f.setColor((short) 0xc);
			// make it bold
			// arial is the default font
			f.setBoldweight(Font.BOLDWEIGHT_BOLD);

			// set font 2 to 10 point type
			f2.setFontHeightInPoints((short) 10);
			// make it red
			f2.setColor((short) Font.COLOR_RED);
			// make it bold
			f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

			f2.setStrikeout(true);

			// set cell stlye
			cs.setFont(f);
			// set the cell format
			cs.setDataFormat(df.getFormat("#,##0.0"));

			// set a thin border
			cs2.setBorderBottom(CellStyle.BORDER_THIN);
			// fill w fg fill color
			cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND);
			// set the cell format to text see DataFormat for a full list
			cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

			// set the font
			cs2.setFont(f2);

			// set the sheet name in Unicode
			wb.setSheetName(
					0,
					"\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F "
							+ "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430");
			// in case of plain ascii
			// wb.setSheetName(0, "HSSF Test");
			// create a sheet with 30 rows (0-29)
			int rownum;
			for (rownum = (short) 0; rownum < 30; rownum++) {
				// create a row
				r = s.createRow(rownum);
				// on every other row
				if ((rownum % 2) == 0) {
					// make the row height bigger (in twips - 1/20 of a point)
					r.setHeight((short) 0x249);
				}

				// r.setRowNum(( short ) rownum);
				// create 10 cells (0-9) (the += 2 becomes apparent later
				for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) {
					// create a numeric cell
					c = r.createCell(cellnum);
					// do some goofy math to demonstrate decimals
					c.setCellValue(rownum
							* 10000
							+ cellnum
							+ (((double) rownum / 1000) + ((double) cellnum / 10000)));

					// create a string cell (see why += 2 in the
					c = r.createCell((short) (cellnum + 1));

					// on every other row
					if ((rownum % 2) == 0) {
						// set this cell to the first cell style we defined
						c.setCellStyle(cs);
						// set the cell's string value to "Test"
						c.setCellValue("Test");
					} else {
						c.setCellStyle(cs2);
						// set the cell's string value to
						// "\u0422\u0435\u0441\u0442"
						c.setCellValue("\u0422\u0435\u0441\u0442");
					}

					// make this column a bit wider
					s.setColumnWidth((short) (cellnum + 1),
							(short) ((50 * 8) / ((double) 1 / 20)));
				}
			}

			// draw a thick black border on the row at the bottom using BLANKS
			// advance 2 rows
			rownum++;
			rownum++;

			r = s.createRow(rownum);

			// define the third style to be the default
			// except with a thick black border at the bottom
			cs3.setBorderBottom(CellStyle.BORDER_THICK);

			// create 50 cells
			for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
				// create a blank type cell (no value)
				c = r.createCell(cellnum);
				// set it to the thick black border style
				c.setCellStyle(cs3);
			}

			// end draw thick black border

			// demonstrate adding/naming and deleting a sheet
			// create a sheet, set its title then delete it
			s = wb.createSheet();
			wb.setSheetName(1, "DeletedSheet");
			wb.removeSheetAt(1);
			// end deleted sheet

			// write the workbook to the output stream
			// close our file (don't blow out our file handles
			wb.write(out);
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void createNormalExcel() {
		SXSSFWorkbook workBook = new SXSSFWorkbook(100);
		Sheet sheet = workBook.createSheet("hello world");
		CellStyle ct = workBook.createCellStyle();
		Font font = workBook.createFont();
		font.setColor(Font.COLOR_NORMAL);
		ct.setFont(font);

		// CellStyle cs2 = workBook.createCellStyle();
		// Font f1 = workBook.createFont();
		// f1.setColor(Font.COLOR_RED);
		// cs2.setFont(f1);
		// cs2.setFillPattern(CellStyle.SOLID_FOREGROUND);
		//
		IndexedColors[] values = IndexedColors.values();

		CellStyle cs2 = workBook.createCellStyle();
		Font f1 = workBook.createFont();
		f1.setColor(Font.COLOR_RED);
		cs2.setFont(f1);
		cs2.setFillPattern(CellStyle.SOLID_FOREGROUND);
		for (int i = 0; i < values.length; i++) {
			Row row = sheet.createRow(i);
			Cell cell = row.createCell(2);
			cs2.setFillForegroundColor(values[i].index);
			cell.setCellStyle(cs2);
			cell.setCellValue(i);
			Cell cell2 = row.createCell(1);
			cell2.setCellValue("ddddddddddddddddddddd");
			cell2.setCellStyle(cs2);
			// for (int j = 0; j < 20; j++) {
			// Cell cell = row.createCell(j);
			// if(j % 2 == 0){
			// cell.setCellStyle(cs2);
			// }else{
			// cell.setCellStyle(ct);
			// }
			// cell.setCellValue(i + " 12222222222222222222222222 " + j);
			// }sheet.setColumnWidth((short)列数,(short)(length*256));
		}
		sheet.autoSizeColumn(1);
		sheet.autoSizeColumn(2);
		System.out.println(sheet.getColumnWidth(1));
		System.out.println(sheet.getColumnWidth(2));

		try {
			FileOutputStream fos = new FileOutputStream("aa.xlsx");
			workBook.write(fos);
			workBook.dispose();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// sheet.setColumnWidth((short)列数,(short)(length*256));
	public void setAutoSize(int columnNum, Sheet sheet) {
		int width = getMaxLengthFromColumn(columnNum, sheet);
		sheet.setColumnWidth(columnNum, width);
	}

	public int getMaxLengthFromColumn(int columnNum, Sheet sheet) {
		for (Row row : sheet) {
			row.getCell(columnNum);
		}
		return 0;
	}

	public static void templateCreatorTest() {
		SXSSFWorkbook wb = new SXSSFWorkbook(100);
		ExcelTemplateCreator c = new SXSSFExcelTemplateCreator(wb);
		ExcelTemplateCreatorContext proxy = new ExcelTemplateCreatorContext(c);
		Set<RecapItem> items = new HashSet<RecapItem>();
		for (int j = 0; j < 10; j++) {
			RecapItem item = new RecapItem("10dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd0" + j, "dsdsds"
					+ j, "ss" + j);
			items.add(item);
		}
		proxy.createDate("Date: ", new Date());
		proxy.createOutstandingItem("Outstanding Items: ", "No");
		proxy.createRecapItemTitle(new String[] { "Name", "Down", "Follow up" });
		proxy.createRecapItem(items);
		proxy.createMisc("MISC", "");
		proxy.autoSizeColumn();
		writeToFile(new File("test.xlsx"), wb);
	}

	public static void writeToFile(File f, SXSSFWorkbook wb) {
		try {
			FileOutputStream fos = new FileOutputStream(f);
			wb.write(fos);
			wb.dispose();
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void testAuto() {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		// HSSFSheet sheet = workbook.createSheet("SheetName");
		// 用于格式化单元格的数据
		HSSFDataFormat format = workbook.createDataFormat();
		// 创建新行(row),并将单元格(cell)放入其中. 行号从0开始计算.
		HSSFRow row = sheet.createRow((short) 1);
		// 设置字体
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 20); // 字体高度
		font.setColor(HSSFFont.COLOR_RED); // 字体颜色
		font.setFontName("黑体"); // 字体
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 宽度
		font.setItalic(true); // 是否使用斜体
		// font.setStrikeout(true); //是否使用划线
		// 设置单元格类型
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中中
															// cellStyle.setWrapText(true);
		// 创建单元格
		HSSFCell cell = row.createCell((short) 1);
		HSSFRichTextString hssfString = new HSSFRichTextString("Hello World!");
		cell.setCellValue(hssfString);// 设置单元格内容
		cell.setCellStyle(cellStyle);// 设置单元格样式
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		// 指定单元格格式：数值、公式或字符串
		// Comment comment = new Comment();
		// cell.setCellComment(comment);//添加注释
		// 格式化数据
		row = sheet.createRow(2);
		cell = row.createCell((short) 2);
		cell.setCellValue(11111.25);
		cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(format.getFormat("0.0"));
		cell.setCellStyle(cellStyle);

		row = sheet.createRow((short) 3);
		cell = row.createCell((short) 3);
		cell.setCellValue(9736279.073);
		cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(format.getFormat("#,##0.0000"));
		cell.setCellStyle(cellStyle);

		sheet.autoSizeColumn(0); // 调整第一列宽度
		sheet.autoSizeColumn(1); // 调整第二列宽度
		sheet.autoSizeColumn(2); // 调整第三列宽度
		sheet.autoSizeColumn(3); // 调整第四列宽度

		try {
			FileOutputStream fileOut = new FileOutputStream("3.xls");
			workbook.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			System.out.println(e.toString());
		}
	}
}
